package dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collections;
import java.util.List;

import javax.sql.DataSource;

import models.game.Game;
import models.players.Player;
import models.user.User;

import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

public class UserDAO {

	DataSource dataSource;
	JdbcTemplate jdbc;

	public void setDataSource(DataSource dataSource) {
		this.dataSource = dataSource;
		jdbc = new JdbcTemplate(dataSource);
	}

	// Check if user with those username and email exists
	// If the user exists check if the password is correct
	public boolean checkUserInDataSource(User user) throws SQLException,
			DataAccessException {
		boolean isCorrect = false;
		String passFromDb = null;
		String SQL = "SELECT password FROM FACTORYDATA.USERS where username = '"
				+ user.getUsername() + "'";
		List<String> pass = jdbc.query(SQL, new RowMapper<String>() {
			@Override
			public String mapRow(ResultSet rs, int rowNum) throws SQLException {
				// TODO Auto-generated method stub
				return rs.getString("password");
			}
		});
		if (!pass.isEmpty()){
			passFromDb = pass.get(0);
			return passFromDb.equals(user.getPassword());
		}
		else{
			return false;
		}
	}

	public boolean areUsernameAndMailFree(String username, String email)
			throws SQLException, DataAccessException {
		String SQLuser = "SELECT username FROM FACTORYDATA.USERS";
		String SQLmail = "SELECT email FROM FACTORYDATA.USERS";
		List<String> user = jdbc.query(SQLuser, new RowMapper<String>() {
			@Override
			public String mapRow(ResultSet rs, int rowNum) throws SQLException {
				// TODO Auto-generated method stub
				return rs.getString("username");
			}
		});
		if (!user.contains(username)) {
			List<String> mail = jdbc.query(SQLmail, new RowMapper<String>() {
				@Override
				public String mapRow(ResultSet rs, int rowNum)
						throws SQLException {
					// TODO Auto-generated method stub
					return rs.getString("email");
				}
			});
			return !mail.contains(email);
		} else {
			return false;
		}
	}
	
	public void insertUserInDataSource(User user)
	{
		String username = user.getUsername();
		String email = user.getEmail();
		String password = user.getPassword();
		String SQL = "INSERT INTO FACTORYDATA.USERS (USERNAME, EMAIL, PASSWORD, WINS, LOSSES) VALUES ('" + username + "', '"
																						   + email + "', '"
																						   + password + "', 0, 0)";
		jdbc.execute(SQL);
	}
	
	public void increaseWins (Game game)
	{
		for (Player p : game.getPlayers().values())
		{
			String username = p.getUsername();
			int wins = 0;
			String SQLSelect = "SELECT WINS FROM FACTORYDATA.USERS WHERE USERNAME = '"+ username +"'";
			List<Integer> winsFromDb = jdbc.query(SQLSelect, new RowMapper<Integer>() {
				@Override
				public Integer mapRow(ResultSet rs, int rowNum) throws SQLException {
					// TODO Auto-generated method stub
					return rs.getInt("wins");
				}
			});
			if (!winsFromDb.isEmpty()){
				wins = winsFromDb.get(0);
				wins++;
			}
			String SQLUpdate = "UPDATE FACTORYDATA.USERS SET WINS = " + wins + " WHERE USERNAME = '"+ username +"'";
			jdbc.execute(SQLUpdate);
		}
	}
	
	public void increaseLosses (Game game)
	{
		for (Player p : game.getPlayers().values())
		{
			String username = p.getUsername();
			int losses = 0;
			String SQLSelect = "SELECT LOSSES FROM FACTORYDATA.USERS WHERE USERNAME = '"+ username +"'";
			List<Integer> lossesFromDb = jdbc.query(SQLSelect, new RowMapper<Integer>() {
				@Override
				public Integer mapRow(ResultSet rs, int rowNum) throws SQLException {
					// TODO Auto-generated method stub
					return rs.getInt("losses");
				}
			});
			if (!lossesFromDb.isEmpty()){
				losses = lossesFromDb.get(0);
				losses++;
			}
			String SQLUpdate = "UPDATE FACTORYDATA.USERS SET LOSSES = " + losses + " WHERE USERNAME = '"+ username +"'";
			jdbc.execute(SQLUpdate);
		}
	}
	
	public User getUser (String username)
	{
		String SQL = "SELECT username, email, wins, losses FROM FACTORYDATA.USERS WHERE USERNAME = '" + username + "'";
		List<User> users = jdbc.query(SQL, new RowMapper<User>() {
			@Override
			public User mapRow(ResultSet rs, int rowNum) throws SQLException {
				User user = new User();
				user.setUsername(rs.getString("username"));
				user.setEmail(rs.getString("email"));
				user.setWins(rs.getInt("wins"));
				user.setLosses(rs.getInt("losses"));
				return user;
			}
		});
		User user = null;
		if (!users.isEmpty()){
			user = users.get(0);
		}
		return user;
	}
	
	public List<User> rankUsers ()
	{
		String SQL = "SELECT username, email, wins, losses FROM FACTORYDATA.USERS";
		List<User> users = jdbc.query(SQL, new RowMapper<User>() {
			@Override
			public User mapRow(ResultSet rs, int rowNum) throws SQLException {
				User user = new User();
				user.setUsername(rs.getString("username"));
				user.setEmail(rs.getString("email"));
				user.setWins(rs.getInt("wins"));
				user.setLosses(rs.getInt("losses"));
				return user;
			}
		});
		Collections.sort(users);
		return users;
	}
	
}
